﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

using System.Text;
using Microsoft.ApplicationBlocks.Data;
using Module.EntitiesInfo;

namespace Module.DataAccess
{
   public class MemberDB
    {

        #region ConnectionString
        private string _connectionString;
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }
        public MemberDB()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["localsql"].ToString();
        }
        #endregion

        #region Methods

        public virtual int Insert(MemberInfo memberInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
         
				new SqlParameter("@UserName", memberInfo.UserName),
				new SqlParameter("@Password", memberInfo.Password),
				new SqlParameter("@Address", memberInfo.Address),
				new SqlParameter("@PhoneNumber", memberInfo.PhoneNumber),
                new SqlParameter("@Email", memberInfo.Email),
				new SqlParameter("@Type1", memberInfo.Type1),
				new SqlParameter("@CreateOn", memberInfo.CreateOn),
			    new SqlParameter("@Type2", memberInfo.Type2),
               	new SqlParameter("@Type3", memberInfo.Type3),
                 new SqlParameter("@Active", memberInfo.Active),
                new SqlParameter("@CodeAcitve", memberInfo.CodeAcitve)
				
			};

            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Member_Insert", parameters);
        }

       
        public virtual void Update(MemberInfo memberInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
                new SqlParameter("@IdMember", memberInfo.IdMember),
				new SqlParameter("@UserName", memberInfo.UserName),
				new SqlParameter("@Password", memberInfo.Password),
				new SqlParameter("@Address", memberInfo.Address),
				new SqlParameter("@PhoneNumber", memberInfo.PhoneNumber),
                new SqlParameter("@Email", memberInfo.Email),
				new SqlParameter("@Type1", memberInfo.Type1),
				new SqlParameter("@CreateOn", memberInfo.CreateOn),
			    new SqlParameter("@Type2", memberInfo.Type2),
               	new SqlParameter("@Type3", memberInfo.Type3),
                new SqlParameter("@Active", memberInfo.Active),
                new SqlParameter("@CodeAcitve", memberInfo.CodeAcitve)
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Member_Update", parameters);
        }


        public virtual void Delete(int idMember)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@IdMember", idMember)
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Member_Delete", parameters);
        }      

        public IDataReader Select(int? type, string para_1, string para_2)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "Member_Select", type, para_1, para_2);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public IDataReader Select(int? type, string para_1)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "Member_Select", type, para_1);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public IDataReader Select(int? type)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "Member_Select", type);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type, string para_1, string para_2)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "Member_Select", type, para_1, para_2).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type, string para_1)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "Member_Select", type, para_1).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "Member_Select", type).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// Tính tổng số bản ghi
        /// </summary>
        /// <param name="keysearch">Từ khóa tìm kiếm</param>
        /// <param name="categoryId">Thể loại câu hỏi</param>
        /// <param name="starDatetime">Ngày bắt đầu</param>
        /// <param name="endDatetime">Ngày kết thúc</param>
        /// <param name="beginRow">Bản ghi đầu tiền</param>
        /// <param name="endRow">Bản ghi cuối</param>
        /// <returns></returns>
        public virtual int GetCount(string keysearch,int active, DateTime starDatetime, DateTime endDatetime, int beginRow, int endRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                          
               new SqlParameter("@keysearch", keysearch),  
               new SqlParameter("@Active",active), 
               new SqlParameter("@starDatetime", starDatetime),
               new SqlParameter("@endDatetime", endDatetime),
               new SqlParameter("@IsTotalRow", 1)
			};

            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Member_Search", parameters);
        }

        public bool CheckAcount(int type, string param1, string param2)
        {
            DataTable dt = new DataTable();
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Type", type),
                new SqlParameter("@Para_1", param1),
                new SqlParameter("@Para_2", param2)
            };
            dt = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "Member_Select", parameters).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
                return true;
            return false;
        }


        /// <summary>
        /// Tìm kiếm
        /// </summary>
        /// <param name="keysearch">Từ khóa tìm kiếm</param>
        /// <param name="categoryId">Thể loại câu hỏi</param>
        /// <param name="starDatetime">Ngày bắt đầu</param>
        /// <param name="endDatetime">Ngày kết thúc</param>
        /// <param name="beginRow">Bản ghi đầu</param>
        /// <param name="endRow">Bản ghi cuối</param>
        /// <param name="orderByColumn">Sắp xếp</param>
        /// <returns></returns>
        public virtual DataTable Search(string keysearch,int active,DateTime starDatetime, DateTime endDatetime, int beginRow, int endRow, string orderByColumn)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                
                new SqlParameter("@Keysearch", keysearch),  
                new SqlParameter("@Active",active), 
                new SqlParameter("@StarDatetime", starDatetime),
                new SqlParameter("@EndDatetime", endDatetime),               
                new SqlParameter("@BeginRow", beginRow),
                new SqlParameter("@EndRow", endRow),
                new SqlParameter("@OrderByColumn", orderByColumn),
				new SqlParameter("@IsTotalRow", 0)
			};

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "Member_Search", parameters).Tables[0];
        }

        //Update khi quên mật khẩu
        public virtual void MemberUpdatePassword(string email, string password)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
               
				new SqlParameter("@Email", email),
			    new SqlParameter("@Password", password)
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Member_UpdatePassword", parameters);
        }
      #endregion
    }
}
